package com.cari.oa.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.cari.oa.domain.SysDeptEntity;
import com.cari.oa.util.StrUtils;

/**
* 
* Title: SoftRiskDao
* Description:风险管理dao	
* Company: cari 
* @author    hjx 
* @date       2013-1-21
*/
@Repository
public class SysDeptDao extends BaseDao<SysDeptEntity> {
	
	/**
	* Title: getPage
	* Description:分页查询
	* @author    hjx 
	* @date       2013-1-21 
	* @param deptNo
	* @param deptName
	* @param page
	* @param size
	* @return
	*/
    @SuppressWarnings("unchecked")
	public List<SysDeptEntity> getPage(String deptNo, String deptName,String pNo, int page, int size) {
    
	    StringBuilder sql = new StringBuilder();
        sql.append(" select * from (");
        sql.append("    select  d.dept_no as deptNo,");       //部门编号
        sql.append("	        d.dept_name as deptName,");   //部门名称
        sql.append("		    d.address,");                 //部门地址
        sql.append("	        d.area,");                    //部门地区
        sql.append("            d.describe,");                //部门描述
        sql.append("            prior d.dept_no pNo,");                              //上级父节点名称
        sql.append("            prior d.dept_name pDeptName,");                          //上级父节点名称
        sql.append("            decode(connect_by_isleaf, 1, 'yes', 'no') isChild");     //是否是子节点
        sql.append("    from  T_SYS_DEPT d");
	    sql.append("    start with d.parent_no is null");                                //从parent为空开始扫描 
		sql.append("    connect by prior d.dept_no = d.parent_no");                      //以dept_no为父列连接parent  
		sql.append("    order siblings by d.dept_no desc");                              //对层次排序  
	    sql.append(" ) de where 1=1 ");
        if(StrUtils.isNotEmpty(deptNo)) {
            sql.append(" and de.deptNo like '%"+deptNo+"%'");
        }
        if(StrUtils.isNotEmpty(deptName)) {
            sql.append(" and de.deptName like '%"+deptName+"%'");
        }
        if(StrUtils.isNotEmpty(pNo)) {
            sql.append(" and de.pNo = '"+pNo+"'");
        }
        if(page == 0 && size == 0){
        	sql.append(" and de.isChild ='no'");
        }
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.addScalar("deptNo");
        query.addScalar("deptName");
        query.addScalar("address");
        query.addScalar("area");
        query.addScalar("pNo");
        query.addScalar("describe");
        query.addScalar("pDeptName");
        query.addScalar("isChild");
        query.setResultTransformer(Transformers.aliasToBean(SysDeptEntity.class));
        List<SysDeptEntity> deptList = new ArrayList<SysDeptEntity>();
        if(page == 0 && size == 0){
			deptList=query.list(); 
        }else{
			deptList=query.setFirstResult((page - 1) * size).setMaxResults(size).list();
        }
		return deptList;
    }
    
    /**
    * Title: getCount
    * Description:查询的记录数
    * @author    hjx 
    * @date       2013-1-21 
    * @param deptNo
    * @param deptName
    * @return
    */
    public long getCount(String deptNo, String deptName,String pNo) {
    	
    	List<Object> params = new ArrayList<Object>();
    	
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select count(*) from  SysDeptEntity d where 1=1 ");
        
        if(StrUtils.isNotEmpty(deptNo)) {
            stringBuilder.append("and d.deptNo like ? ");
            params.add("%" + deptNo + "%");
        }

        if(StrUtils.isNotEmpty(deptName)) {
            stringBuilder.append("and d.deptName like ? ");
            params.add("%" + deptName + "%");
        }
        Query query = getSession().createQuery(stringBuilder.toString());
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i, params.get(i));
        }
        return (Long)query.uniqueResult();
    }

    /**
     * Title: existByDeptNo
     * Description:检查部门编号是否存在
     * @author    hjx 
     * @date       2013-1-22 
     * @param deptNo
     * @return
     */
	public boolean existByDeptNo(String deptNo) {
		    Query query = getSession().createQuery("select count(*) from SysDeptEntity d where d.deptNo = :deptNo");
	        query.setString("deptNo", deptNo);
	        Long count = (Long)query.uniqueResult();
	        return count > 0;
	}

	/**
    * Title: existByDeptName
    * Description:检查部门名称是否存在
    * @author    hjx 
    * @date       2013-1-22 
    * @param deptName
    * @return
    */
	public boolean existByDeptName(String deptName) {
		 Query query = getSession().createQuery("select count(*) from SysDeptEntity d where d.deptName = :deptName");
         query.setString("deptName", deptName);
         Long count = (Long)query.uniqueResult();
         return count > 0;
	}


	/**
	* Title: getByDeptNo
	* Description:根据部门编号获取部门
	* @author    hjx 
	* @date       2013-1-22 
	* @param deptNo
	* @return
	*/
	public SysDeptEntity getByDeptNo(String deptNo) {
		 Query query = getSession().createQuery(" from SysDeptEntity d where d.deptNo = :deptNo");
         query.setString("deptNo", deptNo);
         return (SysDeptEntity)query.list().get(0);
	}

    /**
    * Title: getChildDept
    * Description: 获取某个部门底下的子部门
    * @author    hjx 
    * @date       2013-1-22 
    * @param deptNo
    * @return
    */
	@SuppressWarnings("unchecked")
	public List<SysDeptEntity> getChildDept(String deptNo) {
		 Query query = getSession().createQuery(" from SysDeptEntity d where d.pNo = :deptNo");
         query.setString("deptNo", deptNo);
         return query.list();
	}

}
